#!/bin/sh
###############################################################################
##  Author    : xuezhoyi
##  Name      : edw_mysql_executor.sh
##  Functions : Construct the interface of mysql
##  Purpose   : Construct the interface of mysql
##  Revisions or Comments
##  VER        DATE        AUTHOR           DESCRIPTION
##---------  ----------  ---------------  ------------------------------------ 
##  1.0      2017-05-22  xuezhouyi        1. CREATED THIS SHELL.
###############################################################################

. ${0%`basename ${0}`}edw_parm.sh
. ${0%`basename ${0}`}edw_func.sh

function USAGE(){
    echo -e "\nHow to use this shell script!"
    echo -e '\n${V_SHELL_HOME}/edw_mysql_executor.sh -q "select current_date;"'
    echo -e '\n${V_SHELL_HOME}/edw_mysql_executor.sh -n FSD -p FSD -s 192.168.161.24:1521/DCITS -q "select SYSDATE from dual;"'
}

# define basic parm
V_SCRIPT_NAME=$(basename ${0} | cut -d'.' -f1)
V_OUT_LOGS="${V_SHELL_LOGS}/${V_SCRIPT_NAME}_${V_TIME_STAMP}_$$.log"
V_MYSQL_USER=$(base64 -di ${V_MYSQL_USER})
V_MYSQL_PSWD=$(base64 -di ${V_MYSQL_PSWD})
V_TEMP_VALUE="${V_SHELL_PARM}/VALUE.${V_TIME_STAMP}"

# test connection
#tee ${V_TEMP_VALUE}
function TESTCONN(){
V_RESULT=`mysql -s -h${V_MYSQL_HOST} -u${V_MYSQL_USER} -p${V_MYSQL_PSWD} 2>>/dev/null <<EOF
select 0;
EOF`
if [[ $? -ne 0 ]];then
    #V_TEMP_VALUE=$(cat ${V_TEMP_VALUE})
    #if [[ ${V_TEMP_VALUE} -ne 0 ]];then
    #    echo -e "\nError: Cannot connect to MySQL database, \nPlease check: \n[${V_MYSQL_HOST}:${V_MYSQL_USER}:${V_MYSQL_PSWD}]"
    #    exit 9
    #fi
    exit 9
fi
}

# run sql statement
function RUNSQL(){
V_RESULT=`mysql -s -h${V_MYSQL_HOST} -u${V_MYSQL_USER} -p${V_MYSQL_PSWD} 2>>/dev/null <<EOF
${V_MYSQL_QUERY}
EOF`
if [[ $? -eq 0 ]];then
    if [[ -n ${V_RESULT} ]];then
        echo ${V_RESULT}
    fi
else
    exit 9
fi
}

# run a sql file
function RUNFILE(){
v_result=`mysql -S -L /nolog <<EOF
set echo on

conn ${v_osid}
spool "${V_TEMP_VALUE}"
@${v_file};
spool off
exit
EOF`
# using a value file to judge the return code
if [[ -z "$(grep "ORA" ${V_TEMP_VALUE})" ]];then
    echo -e "${v_result} \n"
    rm -f ${V_TEMP_VALUE}
else
    echo -e "${v_result} \n"
    rm -f ${V_TEMP_VALUE}
    exit 6
fi
}

# export data
function EXPDATA(){
mysql -s -h${V_MYSQL_HOST} -u${V_MYSQL_USER} -p${V_MYSQL_PSWD} -e "${V_EXP_DATA}"
}

# get the parameter
while getopts :n:p:s:q:x:f: args; do
    case ${args} in
        n)
            V_MYSQL_USER=${OPTARG}
        ;;
        p)
            V_MYSQL_PSWD=${OPTARG}
        ;;
        s)
            V_MYSQL_HOST=${OPTARG}
        ;;
        q)
            V_MYSQL_QUERY=${OPTARG}
        ;;
        x)
            V_EXP_DATA=${OPTARG}
        ;;
        f)
            V_MYSQL_FILE=${OPTARG}
        ;;
        ?)
            USAGE
        ;;
    esac
done

# ensure the basic variavle of connection is not null
if [[ -z ${V_MYSQL_USER} || -z ${V_MYSQL_PSWD} || -z ${V_MYSQL_HOST} ]];then
    USAGE
    exit 3
fi

# test connection
TESTCONN

# switch case
if [[ -f ${V_MYSQL_FILE} ]];then
    RUNFILE
elif [[ -n ${V_MYSQL_QUERY} ]];then
    RUNSQL
elif [[ -n ${V_EXP_DATA} ]];then
    EXPDATA
else
    USAGE
fi

exit 0
